package com.my.xq.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.my.xq.domain.DoorplateListOrderQueryParam;
import com.my.xq.domain.DoorplateQueryParam;
import com.my.xq.domain.entity.Doorplate;
import com.my.xq.domain.vo.DoorCount;
import com.sskj.core.query.annotation.DynamicQuery;
import com.sskj.core.repository.GenericRepository;

public interface DoorplateRepository extends GenericRepository<Doorplate, String> {
	@DynamicQuery
	Page<Doorplate> findPage(DoorplateListOrderQueryParam dqp, Pageable pageable);

	@Query(value = "SELECT r.id ,r.court_id ,r.doorplate_no,"
			+ "r.created_time,p.name AS proprietorName FROM xq_doorplate r "
			+ " LEFT JOIN xq_proprietor p on r.proprietor_id=p.id" + " where 1=1 and r.court_id = '${courtId}' "
			+ "order by r.created_time desc ", nativeQuery = true)
	@DynamicQuery
	Page<Doorplate> findSpecialPage(DoorplateQueryParam doorplateQueryParam, Pageable pageable);

	@Query("SELECT r FROM Doorplate r " + " where r.courtId = ?1  order by r.createdTime desc")
	Page<Doorplate> findByxq(String courtId, Pageable pageable);

	@Query("SELECT r FROM Doorplate r " + "where r.proprietorId in("
			+ "SELECT x.id FROM Proprietor x where x.name like CONCAT('%',?1,'%'))" + " order by r.courtId ASC")
	@DynamicQuery
	Page<Doorplate> findByProprietorName(String proprietorName, Pageable pageable);

	@Query("SELECT r FROM Doorplate r " + " where r.doorplateNo like CONCAT('%',?1,'%')"
			+ " AND r.courtId IN (?2) AND r.proprietorId is NULL")
	List<Doorplate> searchDoorPlate(String doorplateNo, List<String> courtIds);

	@Query("SELECT r FROM Doorplate r " + " where " + "  r.proprietorId IN (?1) ")
	List<Doorplate> searchDoorPropri(List<String> courtIds);

	@Query("SELECT r FROM Doorplate r " + " where " + " r.proprietorId IN (?1) and r.isBing=true  ")
	List<Doorplate> searchBindDoorPropri(List<String> courtIds);

	@Transactional(rollbackOn = Exception.class)  
	@Modifying(clearAutomatically = true)
	@Query("UPDATE Doorplate SET proprietorId = ?1, isBing = true, openId = ?3 where id IN (?2)")
	void bindProprietor(String proprietorId, List<String> doorplateIds, String openId);

	@Transactional(rollbackOn = Exception.class)
	@Modifying(clearAutomatically = true)
	@Query("UPDATE Doorplate SET proprietorId = NULL, isBing = false, openId = null where proprietorId = ?1")
	void unbindProprietor(String proprietorId);

	@Query("SELECT r FROM Doorplate r where r.proprietorId = ?1 order by r.courtId,r.doorplateNo")
	List<Doorplate> loadBindDoorplate(String proprietorId);

	/**获取包括授权的门牌号在内的所有*/
	@Query("SELECT r FROM Doorplate r where r.proprietorId = ?1 or r.id in (select a.doorplateId from ProprietorAuth a where a.delFlag = 0 and a.proprietorId = ?1) order by r.courtId,r.doorplateNo")
	List<Doorplate> loadDoorplateIncludeAuth(String proprietorId);

	@Query("SELECT r FROM Doorplate r " + " where r.proprietorId = ?1  and r.courtId = ?2  order by r.createdTime desc")
	List<Doorplate> loadDoorByProAndCourt(String proprietorId, String courtId);

	@Query("SELECT r FROM Doorplate r " + " where  r.courtId = ?1")
	List<Doorplate> LoadBindDoorCoundByCourt(String courtId);

	@Query("SELECT r FROM Doorplate r "
			+ " where  r.courtId = ?1 and r.proprietorId=?2 and r.building=?3 and  r.unit=?4")
	List<Doorplate> LoadDoorPriproCast(String courtId, String proprietorId, String building, String unit);

	@Query("SELECT r FROM Doorplate r " + " where  r.courtId = ?1 and r.proprietorId=?2 and r.building=?3 ")
	List<Doorplate> LoadDoorPriproCast(String courtId, String proprietorId, String building);

	@Query(value = "SELECT * FROM xq_Doorplate r where (r.court_Id = ?1 and r.proprietor_Id= ?2) or (r.id in (select a.doorplate_id from xq_proprietor_auth a where a.proprietor_id = ?2 and a.del_flag = 0))", nativeQuery = true)
	List<Doorplate> LoadDoorPriproCast(String courtId, String proprietorId);

	@Query("SELECT r FROM Doorplate r " + " where  r.courtId = ?1 AND r.unit=?2"
			+ " AND r.building=?3 AND r.isCastUnit=true")
	List<Doorplate> findUnitSignUp(String courtId, String unit, String building);

	@Query("SELECT r FROM Doorplate r " + " where  r.courtId = ?1 AND r.building=?2" + " AND r.isCastBuilding=true")
	List<Doorplate> findBuildingSignUp(String courtId, String building);

	@Query("SELECT t FROM Doorplate t where t.id in(" + "SELECT r.doorPlateId FROM CourtRole_Proprietor r " + " where"
			+ "  r.roleId = (SELECT x.id FROM CourtRole x where x.roleName like"
			+ " CONCAT('%',?1,'%') and x.roleWeight < 100)) AND t.courtId = ?2")
	List<Doorplate> findBuildingRole(String roleName, String courtId);

	@Query("SELECT r FROM Doorplate r " + " where  r.courtId = ?1 AND r.isCastUnit=1"
			+ " AND r.countUnit=(SELECT MAX(countUnit) FROM Doorplate where courtId = ?1 AND isCastUnit=1)")
	List<Doorplate> findAllCaseUnit(String courtId);

	@Query(value = "select building from xq_doorplate where court_id=?1 GROUP BY building ", nativeQuery = true)
	List<String> groupByBuilding(String courtId);

	@Query(value = "select unit from xq_doorplate "
			+ "where court_id=?1 and building=?2 GROUP BY unit ", nativeQuery = true)
	List<String> groupByUnit(String courtId, String building);

	@Query(value = "SELECT r FROM Doorplate r where r.courtId = ?1 AND r.unit=?2 "
			+ "AND r.building=?3 AND r.isCastUnit=true "
			+ "AND r.countUnit=(SELECT MAX(countUnit) from Doorplate where courtId=?1"
			+ " and unit=?2 and building=?3 and isCastUnit=true and countUnit > 0 )")
	List<Doorplate> findMaxUnitVotes(String courtId, String unit, String building);

	@Query(value = " select t from Doorplate t where t.courtId = ?1 and t.building = ?2 and t.unit = ?3 and countUnit > 0 order by t.countUnit desc ")
	List<Doorplate> findUnitVotes(String courtId, String building, String unit);

	@Query(value = "SELECT r FROM Doorplate r where r.courtId = ?1 " + "AND r.building=?2 "
			+ "AND r.countBuilding=(SELECT MAX(countBuilding) from Doorplate where courtId=?1" + " and building=?2 and countBuilding > 0 )")
	List<Doorplate> findMaxBuildingVotes(String courtId, String building);

	@Query(value = " select t from Doorplate t where t.courtId = ?1 and t.building = ?2 and countBuilding > 0 order by t.countBuilding desc ")
	List<Doorplate> findBuildingVotes(String courtId, String building);

	@Query(value = "SELECT r FROM Doorplate r where r.courtId = ?1 "
			+ "AND r.countMember=(SELECT MAX(countMember) from Doorplate where courtId=?1 and countMember > 0 )")
	List<Doorplate> findMaxMemberVotes(String courtId);

	@Query(value = " select t from Doorplate t where t.courtId = ?1 and countMember > 0 order by t.countMember desc ")
	List<Doorplate> findMemberVotes(String courtId);

	@Query(value = "SELECT r FROM Doorplate r where r.courtId = ?1 "
			+ "AND r.CountHead=(SELECT MAX(CountHead) from Doorplate where courtId=?1 and CountHead > 0 )")
	List<Doorplate> findMaxHeadVotes(String courtId);

	@Query(value = " select t from Doorplate t where t.courtId = ?1 and CountHead > 0 order by t.CountHead desc ")
	List<Doorplate> findHeadVotes(String courtId);

	@Query(value = "SELECT r.open_id FROM xq_doorplate r where"
			+ " r.court_id = ?1 AND r.is_bing=1 AND r.open_id is not null" + " GROUP BY r.open_id", nativeQuery = true)
	List<String> findIsBingOpenId(String courtId);

	@Transactional(rollbackOn = Exception.class)
	@Modifying(clearAutomatically = true)
	@Query("UPDATE Doorplate SET openId = ?1" + " where proprietorId = ?2 ")
	void updateOpenIdByProprietorId(String openId, String proprietorId);

	@Query(value = "SELECT d.court_id as courtId FROM xq_doorplate d "
			+ " WHERE (d.proprietor_id = ?1 or d.id in (select a.doorplate_id from xq_proprietor_auth a where a.proprietor_id = ?1 and a.del_flag = 0)) "
			+ "GROUP BY d.court_id ORDER BY d.court_id DESC", nativeQuery = true)
	List<String> getCourtIds(String proprietorId);

	@Query("SELECT r FROM Doorplate r where (r.courtId = ?1 and r.proprietorId=?2) "
			+ " or (r.id in (select a.doorplateId from ProprietorAuth a, Doorplate door where door.id = a.doorplateId and door.courtId = ?1 and a.proprietorId = ?2 and a.delFlag = 0)) "
			+ " order by r.building asc, r.unit asc")
	List<Doorplate> findDoorPriproOrder(String courtId, String proprietorId);

	@Query(value = "SELECT courtId from Doorplate where proprietorId = ?1 GROUP BY courtId")
	List<String> findCourtIdByProprietorId(String proprietorId);

	@Query(value = " select count(d)>0 from Doorplate d where d.proprietorId = ?1 and d.courtId = ?2 and d.isCastUnit = true ")
	boolean isCastUnit(String proprietorId, String corutId);

	@Query(value = " select count(d)>0 from Doorplate d where d.proprietorId = ?1 and d.courtId = ?2 and d.isCastBuilding = true ")
	boolean isCastBuilding(String proprietorId, String corutId);

	@Query(value = " select count(d)>0 from Doorplate d where d.proprietorId = ?1 and d.courtId = ?2 and d.isCastMember = true ")
	boolean isCastMember(String proprietorId, String corutId);

	@Query(value = " select count(d)>0 from Doorplate d where d.proprietorId = ?1 and d.courtId = ?2 and d.isCastHead = true ")
	boolean isCastHead(String proprietorId, String corutId);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 AND door.building = ?3 and door.unit = ?4 and door.is_right_unit = 0 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getCanVoteDoorplateUnit(String proprietorId, String courtId, String building, String unit);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 AND door.building = ?3 and door.is_right_building = 0 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getCanVoteDoorplateBuilding(String proprietorId, String courtId, String building);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 and door.is_right_member = 0 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getCanVoteDoorplateMember(String proprietorId, String courtId);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 and door.is_right_head = 0 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getCanVoteDoorplateHead(String proprietorId, String courtId);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 AND door.building = ?3 and door.unit = ?4 and door.is_right_unit = 1 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getIsVoteDoorplateUnit(String proprietorId, String courtId, String building, String unit);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 AND door.building = ?3 and door.is_right_building = 1 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getIsVoteDoorplateBuilding(String proprietorId, String courtId, String building);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 and door.is_right_member = 1 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getIsVoteDoorplateMember(String proprietorId, String courtId);

	@Query(value = "SELECT * FROM xq_doorplate door WHERE door.court_id = ?2 and door.is_right_head = 1 AND (door.id in ( SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.proprietor_id = ?1 AND auth.auth = 1 ) or (door.id not in (SELECT auth.doorplate_id FROM xq_proprietor_auth auth WHERE auth.authorizer = ?1 AND auth.auth = 1) and door.proprietor_id = ?1))", nativeQuery = true)
	List<Doorplate> getIsVoteDoorplateHead(String proprietorId, String courtId);

	@Query(value = "SELECT r FROM Doorplate r where r.courtId = ?1 AND r.building=?2 AND r.unit=?3 AND r.floor=?4 AND r.no=?5 ")
	Doorplate findByBuildingAndUnit(String courtId, String building, String unit, String floor, String no);

	@Query(value = "DELETE from Doorplate where courtId = ?1 ")
	void delByCourtId(String courtId);

	@Query(value = "select d.id from Doorplate d where d.courtId = ?1 ")
	List<String> findIdByCourtId(String courtId);

	@Query(value = "select count(*) as countOfUnit, d.court_id as courtId, d.building as building, d.unit as unit from "
			+ " xq_doorplate d, (select r.building,r.unit from xq_doorplate r where r.court_id = ?1 GROUP BY r.building,r.unit) as r "
			+ " where d.court_id = ?1 and d.building = r.building and d.unit = r.unit GROUP BY r.building,r.unit order by r.building asc,r.unit asc", nativeQuery = true)
	List<Object[]> findCountDoorByCourtId(String courtId);

}
